dmbasisfandomcom_ru-20200214-history
Indexes
Indexing is a crucial aspect of your application design and development. Too many indexes and the performance of DML will suffer. Too few indexes and the performance of queries (including inserts, updates, and deletes) will suffer. Finding the right mix is critical to your application’s performance. Frequently, I find that indexes are an afterthought in application development. I believe that this is the wrong approach. From the very beginning, if you understand how the data will be used, you should be able to come up with the representative set of indexes you will use in your application. Too many times the approach seems to be to throw the application out there and then see where indexes are needed. This implies you have not taken the time to understand how the data will be used and how many rows you will ultimately be dealing with. You’ll be adding indexes to this system forever as the volume of data grows over time (i.e., you’ll perform reactive tuning). You’ll have indexes that are redundant and never used, and this wastes not only space but also computing resources. A few hours at the start spent properly considering when and how to index your data will save you many hours of “tuning” further down the road (note that I said doing so “will,” not “might,” save you many hours). The basic aim of this chapter is to give an overview of the indexes available for use in Oracle and discuss when and where you might use them. This chapter differs from others in this book in terms of its style and format. Indexing is a huge topic—you could write an entire book on the subject—in part because indexing bridges the developer and DBA roles. The developer must be aware of indexes, how indexes apply to their applications, when to use indexes (and when not to use them), and so on. The DBA is concerned with the growth of an index, the use of storage within an index, and other physical properties. We will be tackling indexes mainly from the standpoint of their practical use in applications. The first half of this chapter represents the basic knowledge I believe you need to make intelligent choices about when to index and what type of index to use. The second half of the chapter answers some of the most frequently asked questions about indexes. The various examples in this chapter require different feature releases of Oracle. When a specific example requires features found in Oracle Enterprise or Personal Edition but not Standard Edition, I’ll specify that. An Overview of Oracle Indexes Oracle provides many different types of indexes for us to use. Briefly, they are as follows: * B*Tree indexes: These are what I refer to as “conventional” indexes. They are by far the most common indexes in use in Oracle and most other databases. Similar in construct to a binary tree, B*Tree indexes provide fast access, by key, to an individual row or range of rows, normally requiring few reads to find the correct row. It is important to note, however, that the “B” in “B*Tree” does not stand for binary but rather for balanced. A B*Tree index is not a binary tree at all, as we’ll see when we look at how one is physically stored on disk. The B*Tree index has several subtypes: :* Index organized tables: These are tables stored in a B*Tree structure. Whereas rows of data in a heap table are stored in an unorganized fashion (data goes wherever there is available space), data in an IOT is stored and sorted by primary key. IOTs behave just like “regular” tables as far as your application is concerned; you use SQL to access them as normal. IOTs are especially useful for information retrieval, spatial, and OLAP applications. We discussed IOTs in some detail in the previous chapter. :* B*Tree cluster indexes: These are a slight variation of conventional B*Tree indexes. They are used to index the cluster keys (see the section “Index Clustered Tables” in Chapter 10) and will not be discussed again in this chapter. Rather than having a key that points to a row, as for a conventional B*Tree, a B*Tree cluster has a cluster key that points to the block that contains the rows related to that cluster key. :* Descending indexes: Descending indexes allow for data to be sorted from “big to small” (descending) instead of “small to big” (ascending) in the index structure. We’ll take a look at why that might be important and how they work. :* Reverse key indexes: These are B*Tree indexes whereby the bytes in the key are “reversed.” Reverse key indexes can be used to obtain a more even distribution of index entries throughout an index that is populated with increasing values. For example, if I am using a sequence to generate a primary key, the sequence will generate values like 987500, 987501, 987502, and so on. These values are sequential, so if I were using a conventional B*Tree index, they would all tend to go the same right-hand-side block, thus increasing contention for that block. With a reverse key index, Oracle will logically index 205789, 105789, 005789, and so on instead. Oracle will reverse the bytes of the data to be stored before placing them in the index, so values that would have been next to each other in the index before the byte reversal will instead be far apart. This reversing of the bytes spreads out the inserts into the index over many blocks.